const router = require('koa-router')()

// 生成雪花ID的方法
const createSnow = function (ctx) {
    let snowFlake = ctx.snowFlake;
    let snowId = snowFlake.createSnow(4);
    return snowId;
}
// 将标准时间转换为YYYY-MM-DD HH:MM:SS类型的
const formTime = (time) => {
    let newtime = new Date(time);
    // console.log(new Date(time), "要进行转换的时间");
    const year = newtime.getFullYear();
    const month = String(newtime.getMonth() + 1).padStart(2, "0");
    const day = String(newtime.getDate()).padStart(2, "0");
    const hours = String(newtime.getHours()).padStart(2, "0");
    const minutes = String(newtime.getMinutes()).padStart(2, "0");
    const seconds = String(newtime.getSeconds()).padStart(2, "0");

    const formattedDate = `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
    return formattedDate;
}

router.prefix('/message')

// 用户获取回复自己的消息
router.post('/getmessage', async (ctx, next) => {
    let { user_id } = ctx.request.body
    console.log(user_id);
    // 搜索老师回复的消息
    let tsql = 'select message.message_id, message.sender, message.recipient, message.content, message.create_time, message.update_time, message.type, message.r_msg_id, message.assignment_id, user.user_name, user.user_auth, teacher.avatar, teacher.signature, assignment.assignment_title from message join user on user.user_id = message.sender join teacher on teacher.user_id = message.sender join assignment on assignment.assignment_id = message.assignment_id where message.recipient = ? or message.sender = ? and message.type = ? ORDER BY message.update_time DESC'
    let tres = await ctx.db.EXCUTE(tsql, [user_id, user_id, '#'])
    // 搜索学生回复的消息
    let ssql = 'select message.message_id, message.sender, message.recipient, message.content, message.create_time, message.update_time, message.type, message.r_msg_id, message.assignment_id, user.user_name, user.user_auth, student.avatar, student.signature, assignment.assignment_title from message join user on user.user_id = message.sender join student on student.user_id = message.sender join assignment on assignment.assignment_id = message.assignment_id where message.recipient = ? or message.sender = ? and message.type = ? ORDER BY message.update_time DESC'
    let sres = await ctx.db.EXCUTE(ssql, [user_id, user_id, '#'])
    // 查询每条回复的点赞数
    let nsql = 'select message_id, up_user_id from favour where message_id IN (select message.message_id from message where message.recipient = ? and message.type = ?)'
    let nres = await ctx.db.EXCUTE(nsql, [user_id, '#'])
    console.log(nres);
    let tlist = tres.map(item => {
        return {
            ...item,
            num: nres.filter(i => i.message_id == item.message_id).length,
            isliked: nres.filter(i => i.up_user_id == item.recipient && i.message_id == item.message_id).length == 0 ? false : true
        }
    })
    let slist = sres.map(item => {
        return {
            ...item,
            num: nres.filter(i => i.message_id == item.message_id).length,
            isliked: nres.filter(i => i.up_user_id == item.recipient && i.message_id == item.message_id).length == 0 ? false : true
        }
    })

    console.log(tres);
    console.log(sres);
    ctx.body = { code: 200, data: [...tlist, ...slist] }
})

// 点赞
router.post('/upvote', async (ctx, next) => {
    let { user_id, message_id, up_user_id } = ctx.request.body
    console.log(user_id);
    console.log(message_id);
    console.log(createSnow(ctx));
    console.log(formTime(new Date()));
    // 先查询
    let sql = 'select id from favour where up_user_id=? and message_id=?'
    let res = await ctx.db.EXCUTE(sql, [up_user_id, message_id])
    if (res.length == 0) {
        let sql = 'insert into favour(id,user_id,message_id,create_time,up_user_id) values(?,?,?,?,?)'
        let res = await ctx.db.EXCUTE(sql, [createSnow(ctx), user_id, message_id, formTime(new Date()), up_user_id])
        if (res.affectedRows != 0) {
            ctx.body = { code: 200, msg: 'upvote succeed' }
        }
    } else {
        let sql = 'delete from favour where up_user_id=? and message_id=?'
        let res = await ctx.db.EXCUTE(sql, [up_user_id, message_id])
        if (res.affectedRows != 0) {
            ctx.body = { code: 201, msg: 'upvoted' }
        }
    }
})

// 回复
router.post('/reply', async (ctx, next) => {
    let { sender, recipient, content, type, r_msg_id, assignment_id } = ctx.request.body
    console.log(`sender:${sender}; recipient:${recipient}; content:${content}; type:${type}; r_msg_id:${r_msg_id}`);
    let sql = 'insert into message (message_id,sender,recipient,content,status,create_time,update_time,type,r_msg_id,assignment_id) values (?,?,?,?,?,?,?,?,?,?)'
    let res = await ctx.db.EXCUTE(sql, [createSnow(ctx), sender, recipient, content, 'unread', formTime(new Date()), formTime(new Date()), '#', r_msg_id, assignment_id])
    console.log(res);
    if (res.affectedRows != 0) {
        ctx.body = { code: 200, msg: '已回复' }
    } else {
        ctx.body = { code: 204, msg: 'sql error' }
    }
})

// 用户获取收到的赞
router.post('/getfavour', async (ctx, next) => {
    let { user_id } = ctx.request.body
    let tsql = 'select favour.message_id, favour.create_time, favour.up_user_id, teacher.avatar, teacher.teacher_name, teacher.user_name, message.content from favour join teacher on teacher.user_id = favour.up_user_id join message on message.message_id = favour.message_id where favour.user_id = ?'
    let ssql = 'select favour.message_id, favour.create_time, favour.up_user_id, student.avatar, student.student_name, student.user_name, message.content from favour join student on student.user_id = favour.up_user_id join message on message.message_id = favour.message_id where favour.user_id = ?'
    let tres = await ctx.db.EXCUTE(tsql, [user_id])
    let sres = await ctx.db.EXCUTE(ssql, [user_id])
    ctx.body = { code: 200, data: [...tres, ...sres] }

})
// 用户获取收到的通知
router.post('/getinfo', async (ctx, next) => {
    let { user_id } = ctx.request.body
    // 搜索管理员发的通知
    let asql = 'select message.message_id, message.sender, message.recipient, message.content, message.create_time, message.update_time, message.type, message.r_msg_id, user.user_name, user.user_auth, admin.avatar from message join user on user.user_id = message.sender join admin on admin.user_id = message.sender where message.recipient = ? and message.type = ? ORDER BY message.update_time DESC'
    let ares = await ctx.db.EXCUTE(asql, [user_id, 'info'])
    // 搜索老师发的通知
    let tsql = 'select message.message_id, message.sender, message.recipient, message.content, message.create_time, message.update_time, message.type, message.r_msg_id, user.user_name, user.user_auth, teacher.avatar, teacher.signature from message join user on user.user_id = message.sender join teacher on teacher.user_id = message.sender where message.recipient = ? and message.type = ? ORDER BY message.update_time DESC'
    let tres = await ctx.db.EXCUTE(tsql, [user_id, 'info'])
    // 搜索学生发的通知
    let ssql = 'select message.message_id, message.sender, message.recipient, message.content, message.create_time, message.update_time, message.type, message.r_msg_id, user.user_name, user.user_auth, student.avatar, student.signature from message join user on user.user_id = message.sender join student on student.user_id = message.sender where message.recipient = ? and message.type = ? ORDER BY message.update_time DESC'
    let sres = await ctx.db.EXCUTE(ssql, [user_id, 'info'])
    ctx.body = { code: 200, data: [...tres, ...sres, ...ares] }

})

module.exports = router